package com.service;

import com.database.DatabaseConnection;
import com.model.Group;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class GroupService {
    private DatabaseConnection dbConnection;
    private List<Group> groups;

    public GroupService(DatabaseConnection dbConnection) {
        this.dbConnection = dbConnection;
    }
    // 获取特定班级的所有小组
    public List<Group> getAllGroupsForClass(String currentClass) {

        String sql = "SELECT * FROM Groups WHERE class_id = (SELECT id FROM Classes WHERE name =?)";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, currentClass);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return groups;
    }
    public int getClassIdFromCurrentClass(String className) {
        String sql = "SELECT id FROM Classes WHERE name = ?";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, className);
            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    return resultSet.getInt("id");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1; // 如果没有找到班级，返回 -1
    }


    // 添加小组
    public void addGroup(String groupName, int classId) {
        String sql = "INSERT INTO Groups (name, class_id) VALUES (?, ?)";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, groupName);
            statement.setInt(2, classId);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 获取小组列表
    public List<Group> getGroupList(int classId) {
        List<Group> groups = new ArrayList<>();
        String sql = "SELECT * FROM Groups WHERE class_id = ?";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, classId);
            try (ResultSet resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    groups.add(new Group(id, name));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return groups;
    }

    // 更新小组信息
    public void updateGroup(int groupId, String newGroupName) {
        String sql = "UPDATE Groups SET name = ? WHERE id = ?";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, newGroupName);
            statement.setInt(2, groupId);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 删除小组
    public void deleteGroup(int groupId) {
        String sql = "DELETE FROM Groups WHERE id = ?";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, groupId);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    // 根据小组名称获取小组列表
    public List<Group> getGroupListByName(String name) {
        List<Group> groups = new ArrayList<>();
        String sql = "SELECT * FROM Groups WHERE name LIKE ?";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, "%" + name + "%");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String groupName = resultSet.getString("name");
                groups.add(new Group(id, groupName));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return groups;
    }

    // 获取所有小组的列表
    public List<Group> getAllGroups() {
        List<Group> groups = new ArrayList<>();
        String sql = "SELECT * FROM Groups WHERE class_id = (SELECT id FROM Classes WHERE name =?)";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String groupName = resultSet.getString("name");
                groups.add(new Group(id, groupName));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return groups;
    }
    // 获取当前班级的所有学生
    public List<String> getAllStudentsForClass(String currentClass) {
        List<String> students = new ArrayList<>();
        try {
            Statement statement = dbConnection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT student_name FROM students WHERE class_id = '" + currentClass + "'"); // 假设表名为 students，字段为 student_name
            while (resultSet.next()) {
                students.add(resultSet.getString("student_name"));
            }
        } catch (Exception e) {
            e.printStackTrace(); // 处理异常
        }
        return students;
    }

    public List<Group> getGroupListByClassName(String className) {
        List<Group> groups = new ArrayList<>();
        String sql = "SELECT * FROM Groups WHERE class_id = ?";
        try (Connection connection = dbConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, className);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Group group = new Group();
                group.setId(resultSet.getInt("id"));
                group.setName(resultSet.getString("name"));
                groups.add(group);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return groups;
    }

}